<?php
//0.检索出每个标题所对应的标签名称
select a.atitle,t.tname from arc a join cencer c on a.aid=c.aid join tag t on c.tid=t.tid;	
//1.向stu表中插入两条数据
insert into stu(name,age) values('老程',18),('老张',20);
//2.将stu表中id=9的数据：性别改为女，年龄改为30
update stu set age=30,sex='女' where sid=9;
//3.将年纪小于30岁的同学年龄+1
update stu set age=age+1 where age>30;
//4.删除年纪最小的同学
delete from stu order by age asc limit 1;
//5.学生表中随机获取5条数据
select * from stu order by rand() limit 5;
//6.给stu表添加一个time字段     int(8)
alter table stu add time int(8) unsigned not null default 0;
//7.将name 字段修改为varchar(30)
alter table stu modify name varchar(30) not null default '';
//8.删除学生表的主键
alter table stu modify sid int unsigned;
alter table stu drop primary key;
//9.给student表添加主键自增
alter table stu add primary key(sid);
alter table stu modify sid int unsigned auto_increment;
//10.查找姓李同学的总数
select count(sid) from stu where name like '李%';
//11.student表和class表，统计每个班有多少学生
select c.cname,count(*) from stu s join class c on s.cid=c.cid group by c.cname;
//12.查找张国宝的同班同学(不包含张国宝)
	//1.在学生表找出张国宝所在的班级id
	//2.在学生表找到cid=3所有同学
	select * from stu s1 join stu s2 on s1.cid=s2.cid;
	select s1.sid,s1.name,s2.sid,s2.name from stu s1 join stu s2 on s1.cid=s2.cid;
	select s1.sid,s1.name,s2.sid,s2.name from stu s1 join stu s2 on s1.cid=s2.cid where s2.name="张国宝";
	select s1.name from stu s1 join stu s2 on s1.cid=s2.cid where s2.name="张国宝";
	select s1.name from stu s1 join stu s2 on s1.cid=s2.cid where s2.name="张国宝" and s1.name != "张国宝";
//13.检索出【宝宝心里苦】这篇文章 有哪些标签
	select * from arc a join arc_tag at on a.aid=at.aid join tag t on at.tid=t.tid;
	select a.atitle,t.tname from arc a join arc_tag at on a.aid=at.aid join tag t on at.tid=t.tid;
	select t.tname from arc a join arc_tag at on a.aid=at.aid join tag t on at.tid=t.tid where a.atitle="宝宝你心里苦不苦";
//14.检索出标签最多的文章(arc   arc_tag  tag)
atitle   count()
 文章		2
	//需要中间表和文章表
	select * from arc a join arc_tag at on a.aid=at.aid group by a.atitle;
	select a.atitle,count(at.tid) from arc a join arc_tag at on a.aid=at.aid group by a.atitle order by count(at.tid) desc;
select a.atitle from arc a join arc_tag at on a.aid=at.aid group by a.atitle order by count(at.tid) desc limit 1;
//1.查找所有1990年之后的女同学1990-10-10【1990】
select * from stu where sex="女" and year(birthday)>=1990;










